package veilingService;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Calendar;

import veilingDomein.Bieding;
import veilingDomein.BiedingException;
import veilingDomein.Categorie;
import veilingDomein.Krediet;
import veilingDomein.Veiling;

public class BiedingService implements IBiedingService {

	DBController db = new DBController();
	public ArrayList<Categorie> categorien = new ArrayList<Categorie>();

	public BiedingService() {

	}

	public void init() {

	}

	@SuppressWarnings("unchecked")
	@Override
	public ArrayList<Categorie> haalCategorienOp() {
		Connection con = db.connect();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery("SELECT * FROM CATEGORIE");
			categorien.clear();
			while (rs.next()) {
				Categorie categorie = new Categorie(rs.getInt("categorie_id"),
						rs.getString("naam"));
				categorien.add(categorie);
			}
		} catch (SQLException e) {
			System.out.println("SQLException caught: " + e.getMessage());
		}
		db.closeConnection();
		return (ArrayList<Categorie>) categorien.clone();
	}

	@Override
	public ArrayList<Categorie> getCategorien() {
		return categorien;
	}

	@Override
	public Categorie getCategorieByNaam(String nm) {
		Connection con = db.connect();
		Statement stmt = null;
		ResultSet rs = null;
		Categorie categorie = null;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery("SELECT * FROM CATEGORIE where naam = '"
					+ nm + "'");
			while (rs.next()) {
				categorie = new Categorie(rs.getInt("categorie_id"),
						rs.getString("naam"));
			}
		} catch (SQLException e) {
			System.out.println("SQLException caught: " + e.getMessage());
		}
		db.closeConnection();
		return categorie;
	}

	@Override
	public int getAantalBiedingenPerGebruiker(int userid) {
		Connection con = db.connect();
		Statement stmt = null;
		ResultSet rs = null;
		int aantal = 0;

		try {
			String query = "SELECT count(*) as aantal from bieding where gebruiker_id = "
					+ userid;
			stmt = con.createStatement();
			rs = stmt.executeQuery(query);
			while (rs.next()) {
				aantal = rs.getInt("aantal");
			}
			db.closeConnection();
		} catch (SQLException e) {
			e.printStackTrace();

		}
		return aantal;

	}

	@Override
	public Bieding getHoogsteBieding(Veiling v) {

		ArrayList<Bieding> bod = new ArrayList<Bieding>();
		bod = v.getBiedingen();
		Bieding bi = null;
		int hoogstebod = 0;
		if (bod != null) {
			for (Bieding b : bod) {
				if (b.getBedrag() > hoogstebod) {
					hoogstebod = b.getBedrag();
					bi = b;
				}
			}
		}
		return bi;
	}

	@Override
	public void addBieding(Bieding bod) {
		Connection con = db.connect();
		String sql = "insert into BIEDING values(?,?,?,?,?)";
		try {
			PreparedStatement pst = con.prepareStatement(sql);
			pst.setInt(1, getNewBiedingId());
			pst.setInt(2, bod.getUser().getId());
			pst.setInt(3, bod.getVeiling().getVeilingId());
			pst.setInt(4, bod.getBedrag());
			pst.setTimestamp(5, new java.sql.Timestamp(Calendar.getInstance()
					.getTimeInMillis()));
			pst.executeUpdate();
			pst.close();
			con.close();
			Krediet k = bod.getUser().getKrediet();
			k.setAantal(k.getAantal() - bod.getBedrag());
			bod.getUser().setKrediet(k);
		} catch (SQLException e) {
		}
		db.closeConnection();
	}

	@Override
	public int getNewBiedingId() {
		Connection con = db.connect();
		int id = 0;
		try {
			Statement stmt2 = con.createStatement();
			ResultSet rs2 = stmt2
					.executeQuery("SELECT max(bieding_id) as id FROM BIEDING");
			while (rs2.next()) {
				id = rs2.getInt("id");
			}

		} catch (SQLException e) {
			System.out.println(e);
		}
		db.closeConnection();
		return ++id;

	}

	@Override
	public ArrayList<Bieding> heeftBieding(Veiling v) {
		ArrayList<Bieding> biedingen = new ArrayList<Bieding>();
		int id = v.getVeilingId();
		String sql = "SELECT * FROM BIEDING WHERE VEILING_ID = '" + id + "'";
		Connection con = db.connect();
		try {
			Statement st = con.createStatement();
			ResultSet res = st.executeQuery(sql);
			while (res.next()) {
				try {
					biedingen
							.add(new Bieding(
									res.getInt("BEDRAG"),
									ServiceProvider.getUserService()
											.getUserByID(
													res.getInt("GEBRUIKER_ID")),
									ServiceProvider.getVeilingService()
											.getVeilingByID(
													res.getInt("VEILING_ID")),
									res.getDate("datum"), res
											.getInt("bieding_id")));
				} catch (BiedingException e) {
					e.printStackTrace();
				}
			}
		} catch (SQLException e) {
			System.out.println("SQL EXCEPTION: " + e);
		}
		db.closeConnection();
		return biedingen;
	}

	@Override
	public Categorie haalCategorieIdOp(int id) {
		Connection con = db.connect();
		Statement stmt = null;
		ResultSet rs = null;
		Categorie categorie = null;
		try {
			stmt = con.createStatement();
			rs = stmt
					.executeQuery("SELECT * FROM CATEGORIE where categorie_id = '"
							+ id + "'");
			while (rs.next()) {
				categorie = new Categorie(rs.getInt("categorie_id"),
						rs.getString("naam"));
			}
		} catch (SQLException e) {
			System.out.println("SQLException caught: " + e.getMessage());
		}
		db.closeConnection();
		return categorie;
	}

	@Override
	public int getNewGoedId() {
		Connection con = db.connect();
		int id = 0;
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt
					.executeQuery("SELECT max(goed_id) as id FROM goed");
			while (rs.next()) {
				id = rs.getInt(1);
			}

		} catch (SQLException e) {
			System.out.println(e);
		}
		db.closeConnection();
		return ++id;
	}

	@Override
	public ArrayList<Bieding> haalBiedingenOp() {
		Connection con = db.connect();
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<Bieding> biedingen = new ArrayList<Bieding>();
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery("SELECT * FROM BIEDING");
			while (rs.next()) {
				Bieding b;
				try {
					b = new Bieding(rs.getInt("BEDRAG"), ServiceProvider
							.getUserService().getUserByID(
									rs.getInt("gebruiker_id")), ServiceProvider
							.getVeilingService().getVeilingByID(
									rs.getInt("VEILING_ID")),
							rs.getDate("DATUM"), rs.getLong("bieding_id"));
					biedingen.add(b);
				} catch (BiedingException e) {
					e.printStackTrace();
				}
			}
			db.closeConnection();
		} catch (SQLException e) {
			System.out.println("SQLException caught: " + e.getMessage());
		}
		db.closeConnection();
		return biedingen;
	}

	@Override
	public ArrayList<Bieding> getHoogsteBiedingen(String periode) {

		Connection con = db.connect();
		Statement stmt = null;
		ResultSet rs = null;
		Bieding b = null;
		ArrayList<Bieding> bied = new ArrayList<Bieding>();

		try {
			String query = "SELECT * FROM (SELECT * from bieding order by bedrag DESC) WHERE rownum <=5 order by bedrag DESC";
			if (periode.equals("vandaag")) {
				query = "SELECT * FROM (SELECT * from bieding order by bedrag DESC) where datum >= SYSDATE -1 and rownum <=5 ORDER BY bedrag DESC";
			} else if (periode.equals("week")) {
				query = "SELECT * FROM (SELECT * from bieding order by bedrag DESC) where datum >= SYSDATE -7 and rownum <=5 ORDER BY bedrag DESC";
			} else if (periode.equals("maand")) {
				query = "SELECT * FROM (SELECT * from bieding order by bedrag DESC) where datum >= SYSDATE -30 and rownum <=5 ORDER BY bedrag DESC";
			}

			stmt = con.createStatement();
			rs = stmt.executeQuery(query);

			while (rs.next()) {
				b = new Bieding(rs.getInt("BEDRAG"), ServiceProvider
						.getUserService()
						.getUserByID(rs.getInt("gebruiker_id")),
						ServiceProvider.getVeilingService().getVeilingByID(
								rs.getInt("VEILING_ID")), rs.getDate("DATUM"),
						rs.getLong("bieding_id"));
				bied.add(b);
			}
			db.closeConnection();

		} catch (SQLException e) {
			e.printStackTrace();

		} catch (BiedingException e) {
			//
			e.printStackTrace();
		}

		db.closeConnection();
		return bied;
	}

	@Override
	public int getNewMediaId() {
		Connection con = db.connect();
		int id = 0;
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt
					.executeQuery("SELECT max(media_id) as id FROM media");
			while (rs.next()) {
				id = rs.getInt(1);
			}

		} catch (SQLException e) {
			System.out.println(e);
		}
		db.closeConnection();
		return ++id;

	}

	@Override
	public ArrayList<Bieding> haalBiedingenPerVeilingOp(int veilingId) {
		Connection con = db.connect();
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<Bieding> biedingen = new ArrayList<Bieding>();
		try {
			String query = "SELECT * from bieding where veiling_id = "
					+ veilingId + "order by bedrag DESC";
			stmt = con.createStatement();
			rs = stmt.executeQuery(query);
			while (rs.next()) {
				Bieding b;
				try {
					b = new Bieding(rs.getInt("BEDRAG"), ServiceProvider
							.getUserService()
							.getUserByID(rs.getInt("gebruiker_id")),
							ServiceProvider.getVeilingService().getVeilingByID(
									rs.getInt("VEILING_ID")), rs.getDate("DATUM"),
							rs.getLong("bieding_id"));
					biedingen.add(b);
				} catch (BiedingException e) {
					e.printStackTrace();
				}
			}
			db.closeConnection();
		} catch (SQLException e) {
			e.printStackTrace();

		}
		return biedingen;
	}

	@Override
	public int getAantalBiedingenPerVeiling(int veilingId) {
		Connection con = db.connect();
		Statement stmt = null;
		ResultSet rs = null;
		int aantal = 0;

		try {
			String query = "SELECT count(*) as aantal from bieding where veiling_id = "
					+ veilingId;
			stmt = con.createStatement();
			rs = stmt.executeQuery(query);
			while (rs.next()) {
				aantal = rs.getInt("aantal");
			}
			db.closeConnection();
		} catch (SQLException e) {
			e.printStackTrace();

		}
		return aantal;
	}

	@Override
	public int getHoogsteBiedingPerVeiling(int veilingId) {
		Connection con = db.connect();
		Statement stmt = null;
		ResultSet rs = null;
		int aantal = 0;

		try {
			String query = "SELECT max(bedrag) as aantal from bieding where veiling_id = "
					+ veilingId;
			stmt = con.createStatement();
			rs = stmt.executeQuery(query);
			while (rs.next()) {
				aantal = rs.getInt("aantal");
			}
			db.closeConnection();
		} catch (SQLException e) {
			e.printStackTrace();

		}
		return aantal;
	}
	
	public String[] getInkomstenOverzicht(String maand){
		Connection con = db.connect();
		String[] i = new String[3];
		int ia = 0;
		try{
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT sum(aantal) as aantal, count(aantal) as totaal FROM KREDIETFACTUUR WHERE (select to_char(datum, 'MON') from dual) = '" + maand + "'");
			while(rs.next()){
				i[0] = "" + rs.getInt("aantal");
				i[1] = "" + rs.getInt("totaal");
				ia = rs.getInt("aantal");
			}
		} catch (SQLException e){
			System.out.println(e);
		}
		double t =  ((ia * 0.1) /100);

		DecimalFormat df = new DecimalFormat();
		df.applyPattern("##,##");
		
		i[2] = df.format(t);
		return i;
}

}
